﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Reflection;
using GFrame.Data;

namespace GFrame.Shadow.Search
{

    /// <summary>
    /// ShadowSearch
    /// Jay.Gu
    /// 2009-4-27
    /// 最后修改:2010-5-30
    /// </summary>
    /// <typeparam name="T">数据表实体对象</typeparam>
    public class ShadowSearch<T>: IDisposable
    {
        Type _type;

        private int _iMode = 0; //0单实体类查询，1外键属性关联查询(暂时未实现)

        private string _strConnectStringName = "";

        /// <summary>
        /// Config文件中的链接字符串名称
        /// </summary>
        public String ConnectionName
        {
            set
            {
                _strConnectStringName = value;
            }
            get
            {
                return _strConnectStringName;
            }
        }

        private SQLReader _sr;

        private string _strTable;

        public ShadowSearch()
        {

            _type = typeof(T);

            _strTable = (_type.GetCustomAttributes(typeof(GTable), false)[0] as GTable).TableName;
        }

        public void Dispose()
        {
            if (_sr != null) _sr.Dispose();

            _type = null;


            
        }

        /// <summary>
        /// 执行SQL结果转换成实体类列表
        /// </summary>
        /// <param name="SQL">SQL Command Text</param>
        /// <returns></returns>
        public List<T> DoSQLCommand(string SQL)
        {

            List<T> lst = null;

            if (_sr == null)
                _sr = new SQLReader();

            DataTable dt = new DataTable();

            _sr.Fill(SQL, _strConnectStringName, dt);

            lst = _CreateInstance(dt);

            _sr.Dispose();

            return lst;
        }

        /// <summary>
        /// 执行SQL结果转换成实体类列表
        /// </summary>
        /// <param name="SQL">SQL Command Text</param>
        /// <param name="pl">参数列表</param>
        /// <returns></returns>
        public List<T> DoSQLCommand(string SQL, ParamList pl)
        {

            List<T> lst = null;

            if (_sr == null)
                _sr = new SQLReader();

            DataTable dt = new DataTable();

            _sr.Fill(SQL, _strConnectStringName, dt, pl);

            lst = _CreateInstance(dt);

            _sr.Dispose();

            return lst;
        }

        /// <summary>
        /// 按条件查询
        /// 查询语句参数值示例：id = @id,ParamList对象中则需包含一名叫@id的参数值，并赋值。
        /// </summary>
        /// <param name="exp">条件表达式，不需要加WHERE</param>
        /// <param name="pl">参数值</param>
        /// <returns></returns>
        public List<T> Search(string exp, ParamList pl)
        {

            List<T> lst = null;

            string strSQL = "SELECT * FROM " + _strTable;

            if (_sr == null)
                _sr = new SQLReader();

            if (!string.IsNullOrEmpty(exp))
            {

                DataTable dt = new DataTable();

                strSQL += " WHERE " + exp;

                _sr.Fill(strSQL, _strConnectStringName, dt, pl);

                lst = _CreateInstance(dt);
            }

            _sr.Dispose();

            return lst;
        }

        /// <summary>
        /// 按条件查询
        /// </summary>
        /// <param name="exp">条件表达式，不需要加WHERE</param>
        /// <returns></returns>
        public List<T> Search(string exp)
        {

            List<T> lst = null;

            string strSQL = "SELECT * FROM " + _strTable;

            if (_sr == null)
                _sr = new SQLReader();

            if (!string.IsNullOrEmpty(exp))
            { strSQL += " WHERE " + exp; }

            DataTable dt = new DataTable();

            _sr.Fill(strSQL, _strConnectStringName, dt);

            lst = _CreateInstance(dt);

            _sr.Dispose();

            return lst;
        }

        /// <summary>
        /// 按条件自定义列查询
        /// </summary>
        /// <param name="exp"></param>
        /// <param name="Columns"></param>
        /// <returns></returns>
        public List<T> Search(string exp, string Columns)
        {

            List<T> lst = null;

            string strSQL = "SELECT " + Columns + " FROM " + _strTable;

            if (_sr == null)
                _sr = new SQLReader();

            if (!string.IsNullOrEmpty(exp))
            { strSQL += " WHERE " + exp; }

            DataTable dt = new DataTable();

            _sr.Fill(strSQL, _strConnectStringName, dt);

            lst = _CreateInstance(dt);

            _sr.Dispose();

            return lst;
        }

        /// <summary>
        /// 获取PK列表
        /// </summary>
        /// <returns></returns>
        private List<string> GetPK()
        {

            List<string> lstPK = new List<string>();

            foreach (PropertyInfo pi in _type.GetProperties())
            {
                GField field = (pi.GetCustomAttributes(typeof(GField), false)[0] as GField);

                if (field != null && field.FieldType == FieldTypeEnum.PK)
                {
                    lstPK.Add(field.FieldName);
                }
            }

            return lstPK;
        }

        /// <summary>
        /// 生成SQL查询语句
        /// </summary>
        /// <param name="exp"></param>
        /// <param name="Col"></param>
        /// <param name="Rule"></param>
        /// <param name="CountSQLCommand"></param>
        /// <returns></returns>
        private string __getSelectCommand(string exp, string Col, ShadowRule Rule, out string CountSQLCommand)
        {

            string strSQL = string.Empty;
            string strCountSQL = string.Empty;

            if (string.IsNullOrEmpty(Col))
                Col = "*";

            strSQL = "SELECT TOP " + Rule.PageSize.ToString() + " " + Col + " FROM " + _strTable;
            strCountSQL = "SELECT COUNT(1) FROM " + _strTable;

            if (!string.IsNullOrEmpty(exp))
            {
                strSQL += " WHERE " + exp;
                strCountSQL += " WHERE " + exp;
            }

            List<string> lstPK = GetPK();

            if (!string.IsNullOrEmpty(exp))
                strSQL += " AND ";
            else
                strSQL += " WHERE ";

            int iTop = 0;

            string strPKList = "";

            string strPK = string.Empty;

            foreach (string pk in lstPK)
            {
                strPKList += pk + ",";
                strPK = pk;
            }

            if (!string.IsNullOrEmpty(Rule.OrderString))
                strPKList = Rule.OrderString;

            strPKList = strPKList.TrimEnd(',');

            iTop = Rule.PageSize * (Rule.PageIndex - 1);

            if (string.IsNullOrEmpty(exp))
                strSQL += strPK + " NOT IN(SELECT TOP " + iTop.ToString() + " " + strPK + " FROM " + _strTable + " ORDER BY " + strPKList + ")";
            else
                strSQL += strPK + " NOT IN(SELECT TOP " + iTop.ToString() + " " + strPK + " FROM " + _strTable + " WHERE " + exp + " ORDER BY " + strPKList + ")";

            strSQL += " ORDER BY " + strPKList;

            CountSQLCommand = strCountSQL;

            return strSQL;

        }


        private void __setShadowRule(ShadowRule rule, object obj)
        {
            int iRecordCount = 0;
            int.TryParse(obj.ToString(), out iRecordCount);

            rule.RecordCount = iRecordCount;

            rule.PageCount = rule.RecordCount / rule.PageSize;

            if (rule.RecordCount % rule.PageSize != 0)
                rule.PageCount++;


        }

        /// <summary>
        /// 按条件分页查询
        /// </summary>
        /// <param name="exp">WHERE后面的语句</param>
        /// <param name="Rule">分页对象</param>
        /// <returns></returns>
        public List<T> Search(string exp, ShadowRule Rule)
        {

            List<T> lst = null;

            string strSQL = string.Empty;
            string strCountSQL = string.Empty;

            if (_sr == null)
                _sr = new SQLReader();

            if (Rule.PageIndex <= 0) Rule.PageIndex = 1;
            if (Rule.PageSize <= 0) Rule.PageSize = 10;

            strSQL = __getSelectCommand(exp, string.Empty, Rule, out strCountSQL);

            DataTable dt = new DataTable();

            _sr.Fill(strSQL, _strConnectStringName, dt);

            object obj = _sr.ExecuteScalar(strCountSQL, _strConnectStringName);

            if (obj != null)
            {
                __setShadowRule(Rule, obj);
            }

            lst = _CreateInstance(dt);

            _sr.Dispose();

            return lst;
        }

        /// <summary>
        /// 带参数的条件分页查询
        /// </summary>
        /// <param name="exp">WHERE后面的语句</param>
        /// <param name="Rule">分页对象</param>
        /// <param name="pl">参数列</param>
        /// <returns></returns>
        public List<T> Search(string exp, ShadowRule Rule, ParamList pl)
        {

            List<T> lst = null;

            string strSQL = string.Empty;
            string strCountSQL = string.Empty;

            if (_sr == null)
                _sr = new SQLReader();

            if (Rule.PageIndex <= 0) Rule.PageIndex = 1;
            if (Rule.PageSize <= 0) Rule.PageSize = 10;

            strSQL = __getSelectCommand(exp, string.Empty, Rule, out strCountSQL);

            DataTable dt = new DataTable();

            _sr.Fill(strSQL, _strConnectStringName, dt, pl);

            object obj = _sr.ExecuteScalar(strCountSQL, pl, _strConnectStringName);

            if (obj != null)
            {
                __setShadowRule(Rule, obj);
            }

            lst = _CreateInstance(dt);

            _sr.Dispose();

            return lst;
        }


        /// <summary>
        /// 自定义列分页查询
        /// </summary>
        /// <param name="exp"></param>
        /// <param name="Colunms"></param>
        /// <param name="Rule"></param>
        /// <returns></returns>
        public List<T> Search(string exp, string Colunms, ShadowRule Rule)
        {

            List<T> lst = null;

            string strSQL = string.Empty;
            string strCountSQL = string.Empty;

            if (_sr == null)
                _sr = new SQLReader();

            if (Rule.PageIndex <= 0) Rule.PageIndex = 1;
            if (Rule.PageSize <= 0) Rule.PageSize = 10;

            strSQL = __getSelectCommand(exp, Colunms, Rule, out strCountSQL);

            DataTable dt = new DataTable();

            _sr.Fill(strSQL, _strConnectStringName, dt);

            object obj = _sr.ExecuteScalar(strCountSQL, _strConnectStringName);

            if (obj != null)
            {
                __setShadowRule(Rule, obj);

            }

            lst = _CreateInstance(dt);

            _sr.Dispose();

            return lst;
        }

        /// <summary>
        /// 自定义列分页带参数查询
        /// </summary>
        /// <param name="exp">WHERE条件表达式</param>
        /// <param name="Colunms">自定义列</param>
        /// <param name="Rule">分页规则对象</param>
        /// <param name="pl">参数列</param>
        /// <returns></returns>
        public List<T> Search(string exp, string Colunms, ShadowRule Rule, ParamList pl)
        {

            List<T> lst = null;

            string strSQL = string.Empty;
            string strCountSQL = string.Empty;

            if (_sr == null)
                _sr = new SQLReader();

            if (Rule.PageIndex <= 0) Rule.PageIndex = 1;
            if (Rule.PageSize <= 0) Rule.PageSize = 10;

            strSQL = __getSelectCommand(exp, Colunms, Rule, out strCountSQL);

            DataTable dt = new DataTable();

            _sr.Fill(strSQL, _strConnectStringName, dt, pl);

            object obj = _sr.ExecuteScalar(strCountSQL, pl, _strConnectStringName);

            if (obj != null)
            {
                __setShadowRule(Rule, obj);

            }

            lst = _CreateInstance(dt);

            _sr.Dispose();

            return lst;
        }

        /// <summary>
        /// 实体类转换代码
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private List<T> _CreateInstance(DataTable dt)
        {
            List<T> lst = new List<T>();

            foreach (DataRow dr in dt.Rows)
            {
                T item = (T)_type.Assembly.CreateInstance(_type.FullName); //(T)Assembly.Load(_type.Namespace).CreateInstance(_type.FullName);

                foreach (PropertyInfo info in _type.GetProperties())
                {
                    GField gf = (info.GetCustomAttributes(typeof(GField), false)[0] as GField);

                    string strCol = gf.FieldName;

                    strCol = strCol.Replace("[", "");
                    strCol = strCol.Replace("]", "");

                    try
                    {
                        if (dr[strCol] != DBNull.Value)
                            info.SetValue(item, dr[strCol], null);
                    }
                    catch{

                    }
                }

                lst.Add(item);
            }

            return lst;
        }

    }
}
